"""
联系人查询服务
处理联系人的查询、导入、导出等业务逻辑
"""

import json
import re
from typing import List, Optional, Dict, Any, Tuple
from sqlalchemy.orm import Session
from sqlalchemy import func, or_, and_
from fastapi import HTTPException, status
import pymysql
import pandas as pd

from models.contact import Contact, ContactGroup, ContactGroupMember
from models.data_source import DataSource
from schemas.contact import (
    ContactCreate, ContactUpdate, ContactResponse, ContactList,
    ContactQuery, ContactQueryResponse, ContactImportRequest, ContactImportResponse,
    ContactPreviewRequest, ContactPreviewResponse, ContactStatistics
)
from services.data_source_service import DataSourceService


class ContactService:
    """联系人服务类"""

    @staticmethod
    def search_contacts(db: Session, search_query: str, limit: int = 10, user_id: int = None) -> List[Dict[str, Any]]:
        """搜索联系人 - 简化版本，只搜索姓名和邮箱"""
        try:
            print(f"搜索联系人: query='{search_query}', user_id={user_id}, limit={limit}")

            # 构建基础查询
            query = db.query(Contact)

            # 限制用户范围
            if user_id:
                query = query.filter(Contact.user_id == user_id)

            # 搜索条件：姓名或邮箱包含关键词
            if search_query and search_query.strip():
                search_term = f"%{search_query.strip()}%"
                query = query.filter(
                    or_(
                        Contact.name.ilike(search_term),
                        Contact.email.ilike(search_term)
                    )
                )

            # 执行查询
            contacts = query.order_by(Contact.name).limit(limit).all()

            print(f"找到 {len(contacts)} 个联系人")

            # 转换为字典格式
            result = []
            for contact in contacts:
                result.append({
                    "id": contact.id,
                    "name": contact.name,
                    "email": contact.email,
                    "phone": contact.phone or "",
                    "company": contact.company or "",
                    "department": contact.department or "",
                    "source": "联系人库"
                })
                print(f"  - {contact.name} ({contact.email})")

            return result

        except Exception as e:
            print(f"搜索联系人异常: {str(e)}")
            import traceback
            traceback.print_exc()
            return []

    @staticmethod
    def _search_from_data_sources(db: Session, query: str, limit: int, user_id: int) -> List[Dict[str, Any]]:
        """从数据源中搜索联系人"""
        try:
            # 获取用户的数据源
            data_sources = db.query(DataSource).filter(DataSource.user_id == user_id).all()

            result = []
            for data_source in data_sources:
                if len(result) >= limit:
                    break

                try:
                    # 根据数据源类型搜索
                    if data_source.type.value == 'mysql':
                        contacts = ContactService._search_mysql_contacts(data_source, query, limit - len(result))
                        for contact in contacts:
                            contact["source"] = data_source.name
                        result.extend(contacts)
                except Exception as e:
                    print(f"从数据源 {data_source.name} 搜索失败: {str(e)}")
                    continue

            return result

        except Exception as e:
            print(f"从数据源搜索联系人异常: {str(e)}")
            return []

    @staticmethod
    def _search_mysql_contacts(data_source: DataSource, query: str, limit: int) -> List[Dict[str, Any]]:
        """从MySQL数据源搜索联系人"""
        try:
            config = json.loads(data_source.config) if isinstance(data_source.config, str) else data_source.config

            connection = pymysql.connect(
                host=config.get('host'),
                port=config.get('port', 3306),
                user=config.get('username'),
                password=config.get('password'),
                database=config.get('database'),
                charset=config.get('charset', 'utf8mb4')
            )

            result = []
            with connection.cursor() as cursor:
                # 获取所有表
                cursor.execute("SHOW TABLES")
                tables = [table[0] for table in cursor.fetchall()]

                for table in tables:
                    if len(result) >= limit:
                        break

                    # 获取表结构
                    cursor.execute(f"DESCRIBE {table}")
                    columns = [col[0] for col in cursor.fetchall()]

                    # 查找可能的姓名和邮箱字段
                    name_fields = [col for col in columns if any(keyword in col.lower() for keyword in ['name', 'username', '姓名', '用户名'])]
                    email_fields = [col for col in columns if any(keyword in col.lower() for keyword in ['email', 'mail', '邮箱', '邮件'])]

                    if not email_fields:
                        continue

                    # 构建搜索SQL
                    search_conditions = []
                    for field in name_fields + email_fields:
                        search_conditions.append(f"{field} LIKE %s")

                    if search_conditions:
                        sql = f"SELECT {', '.join(name_fields + email_fields)} FROM {table} WHERE {' OR '.join(search_conditions)} LIMIT {limit - len(result)}"
                        params = [f"%{query}%"] * len(search_conditions)

                        cursor.execute(sql, params)
                        rows = cursor.fetchall()

                        for row in rows:
                            contact = {
                                "id": f"{table}_{hash(str(row))}",  # 生成唯一ID
                                "name": "",
                                "email": "",
                                "source": f"{data_source.name}.{table}"
                            }

                            # 映射字段值
                            for i, field in enumerate(name_fields + email_fields):
                                if i < len(row) and row[i]:
                                    if field in name_fields:
                                        contact["name"] = str(row[i])
                                    elif field in email_fields:
                                        contact["email"] = str(row[i])

                            if contact["email"]:  # 必须有邮箱
                                result.append(contact)

            connection.close()
            return result

        except Exception as e:
            print(f"MySQL搜索异常: {str(e)}")
            return []

    @staticmethod
    def create_contact(db: Session, contact_data: ContactCreate, user_id: int) -> Contact:
        """创建联系人"""
        # 检查邮箱是否已存在
        existing = db.query(Contact).filter(
            Contact.email == contact_data.email,
            Contact.user_id == user_id
        ).first()
        
        if existing:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail="该邮箱地址已存在"
            )
        
        # 创建联系人
        db_contact = Contact(
            name=contact_data.name,
            email=contact_data.email,
            phone=contact_data.phone,
            company=contact_data.company,
            department=contact_data.department,
            position=contact_data.position,
            user_id=user_id
        )
        
        db.add(db_contact)
        db.commit()
        db.refresh(db_contact)
        return db_contact
    
    @staticmethod
    def get_contacts(
        db: Session, 
        user_id: int, 
        page: int = 1, 
        size: int = 10,
        search: Optional[str] = None,
        company: Optional[str] = None,
        department: Optional[str] = None
    ) -> ContactList:
        """获取联系人列表"""
        query = db.query(Contact).filter(Contact.user_id == user_id)
        
        # 搜索过滤
        if search:
            search_pattern = f"%{search}%"
            query = query.filter(
                or_(
                    Contact.name.like(search_pattern),
                    Contact.email.like(search_pattern),
                    Contact.phone.like(search_pattern)
                )
            )
        
        # 公司过滤
        if company:
            query = query.filter(Contact.company.like(f"%{company}%"))
        
        # 部门过滤
        if department:
            query = query.filter(Contact.department.like(f"%{department}%"))
        
        # 总数
        total = query.count()
        
        # 分页
        items = query.order_by(Contact.created_at.desc()).offset((page - 1) * size).limit(size).all()
        
        # 计算总页数
        pages = (total + size - 1) // size
        
        return ContactList(
            items=[ContactResponse.from_orm(item) for item in items],
            total=total,
            page=page,
            size=size,
            pages=pages
        )
    
    @staticmethod
    def get_contact(db: Session, contact_id: int, user_id: int) -> Optional[Contact]:
        """获取单个联系人"""
        return db.query(Contact).filter(
            Contact.id == contact_id,
            Contact.user_id == user_id
        ).first()
    
    @staticmethod
    def update_contact(
        db: Session, 
        contact_id: int, 
        contact_data: ContactUpdate, 
        user_id: int
    ) -> Optional[Contact]:
        """更新联系人"""
        db_contact = ContactService.get_contact(db, contact_id, user_id)
        if not db_contact:
            return None
        
        # 检查邮箱是否已存在（排除自己）
        if contact_data.email:
            existing = db.query(Contact).filter(
                Contact.email == contact_data.email,
                Contact.user_id == user_id,
                Contact.id != contact_id
            ).first()
            
            if existing:
                raise HTTPException(
                    status_code=status.HTTP_400_BAD_REQUEST,
                    detail="该邮箱地址已存在"
                )
        
        # 更新字段
        update_data = contact_data.dict(exclude_unset=True)
        for field, value in update_data.items():
            setattr(db_contact, field, value)
        
        db.commit()
        db.refresh(db_contact)
        return db_contact
    
    @staticmethod
    def delete_contact(db: Session, contact_id: int, user_id: int) -> bool:
        """删除联系人"""
        db_contact = ContactService.get_contact(db, contact_id, user_id)
        if not db_contact:
            return False
        
        db.delete(db_contact)
        db.commit()
        return True
    
    @staticmethod
    def query_contacts_from_data_source(
        db: Session, 
        query_request: ContactQuery, 
        user_id: int
    ) -> ContactQueryResponse:
        """从数据源查询联系人"""
        # 获取数据源
        data_source = DataSourceService.get_data_source(db, query_request.data_source_id, user_id)
        if not data_source:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail="数据源不存在"
            )
        
        try:
            config = json.loads(data_source.config)
            
            if data_source.type == "mysql":
                return ContactService._query_mysql_contacts(
                    config, query_request, data_source.name, data_source.type
                )
            elif data_source.type == "csv":
                return ContactService._query_csv_contacts(
                    config, query_request, data_source.name, data_source.type
                )
            elif data_source.type == "excel":
                return ContactService._query_excel_contacts(
                    config, query_request, data_source.name, data_source.type
                )
            else:
                raise HTTPException(
                    status_code=status.HTTP_400_BAD_REQUEST,
                    detail="不支持的数据源类型"
                )
        except Exception as e:
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail=f"查询联系人失败: {str(e)}"
            )

    @staticmethod
    def _query_mysql_contacts(
        config: Dict[str, Any],
        query_request: ContactQuery,
        data_source_name: str,
        data_source_type: str
    ) -> ContactQueryResponse:
        """从MySQL数据源查询联系人"""
        connection = pymysql.connect(
            host=config['host'],
            port=config['port'],
            user=config['username'],
            password=config['password'],
            database=config['database'],
            charset=config.get('charset', 'utf8mb4')
        )

        try:
            with connection.cursor() as cursor:
                # 获取所有表名
                cursor.execute("SHOW TABLES")
                tables = [row[0] for row in cursor.fetchall()]

                if not tables:
                    return ContactQueryResponse(
                        contacts=[],
                        total=0,
                        page=query_request.page,
                        size=query_request.size,
                        pages=0,
                        columns=[],
                        data_source_name=data_source_name,
                        data_source_type=data_source_type
                    )

                # 使用第一个表进行查询
                table_name = tables[0]

                # 构建查询条件
                where_conditions = []
                params = []

                if query_request.search:
                    # 获取列信息
                    cursor.execute(f"DESCRIBE `{table_name}`")
                    columns = [row[0] for row in cursor.fetchall()]

                    # 在所有文本列中搜索
                    search_conditions = []
                    for col in columns:
                        search_conditions.append(f"`{col}` LIKE %s")
                        params.append(f"%{query_request.search}%")

                    if search_conditions:
                        where_conditions.append(f"({' OR '.join(search_conditions)})")

                # 添加自定义过滤条件
                if query_request.filters:
                    for field, value in query_request.filters.items():
                        where_conditions.append(f"`{field}` = %s")
                        params.append(value)

                # 构建WHERE子句
                where_clause = ""
                if where_conditions:
                    where_clause = "WHERE " + " AND ".join(where_conditions)

                # 获取总数
                count_sql = f"SELECT COUNT(*) FROM `{table_name}` {where_clause}"
                cursor.execute(count_sql, params)
                total = cursor.fetchone()[0]

                # 获取列信息
                cursor.execute(f"DESCRIBE `{table_name}`")
                columns = [row[0] for row in cursor.fetchall()]

                # 获取数据
                offset = (query_request.page - 1) * query_request.size
                data_sql = f"SELECT * FROM `{table_name}` {where_clause} LIMIT %s OFFSET %s"
                cursor.execute(data_sql, params + [query_request.size, offset])
                rows = cursor.fetchall()

                # 转换数据格式
                contacts = []
                for row in rows:
                    contact = {}
                    for i, col in enumerate(columns):
                        contact[col] = str(row[i]) if row[i] is not None else ""
                    contacts.append(contact)

                # 计算总页数
                pages = (total + query_request.size - 1) // query_request.size

                return ContactQueryResponse(
                    contacts=contacts,
                    total=total,
                    page=query_request.page,
                    size=query_request.size,
                    pages=pages,
                    columns=columns,
                    data_source_name=data_source_name,
                    data_source_type=data_source_type
                )
        finally:
            connection.close()

    @staticmethod
    def _query_csv_contacts(
        config: Dict[str, Any],
        query_request: ContactQuery,
        data_source_name: str,
        data_source_type: str
    ) -> ContactQueryResponse:
        """从CSV数据源查询联系人"""
        # 读取CSV文件
        df = pd.read_csv(
            config['file_path'],
            encoding=config.get('encoding', 'utf-8'),
            delimiter=config.get('delimiter', ','),
            header=0 if config.get('has_header', True) else None
        )

        # 处理列名
        if not config.get('has_header', True):
            df.columns = [f"Column_{i+1}" for i in range(len(df.columns))]

        # 应用搜索过滤
        if query_request.search:
            search_mask = df.astype(str).apply(
                lambda x: x.str.contains(query_request.search, case=False, na=False)
            ).any(axis=1)
            df = df[search_mask]

        # 应用自定义过滤
        if query_request.filters:
            for field, value in query_request.filters.items():
                if field in df.columns:
                    df = df[df[field].astype(str).str.contains(str(value), case=False, na=False)]

        total = len(df)

        # 分页
        start_idx = (query_request.page - 1) * query_request.size
        end_idx = start_idx + query_request.size
        df_page = df.iloc[start_idx:end_idx]

        # 转换数据格式
        contacts = []
        for _, row in df_page.iterrows():
            contact = {}
            for col in df.columns:
                contact[col] = str(row[col]) if pd.notna(row[col]) else ""
            contacts.append(contact)

        # 计算总页数
        pages = (total + query_request.size - 1) // query_request.size

        return ContactQueryResponse(
            contacts=contacts,
            total=total,
            page=query_request.page,
            size=query_request.size,
            pages=pages,
            columns=df.columns.tolist(),
            data_source_name=data_source_name,
            data_source_type=data_source_type
        )

    @staticmethod
    def _query_excel_contacts(
        config: Dict[str, Any],
        query_request: ContactQuery,
        data_source_name: str,
        data_source_type: str
    ) -> ContactQueryResponse:
        """从Excel数据源查询联系人"""
        # 读取Excel文件
        df = pd.read_excel(
            config['file_path'],
            sheet_name=config.get('sheet_name'),
            header=0 if config.get('has_header', True) else None,
            skiprows=config.get('start_row', 0)
        )

        # 处理列名
        if not config.get('has_header', True):
            df.columns = [f"Column_{i+1}" for i in range(len(df.columns))]

        # 应用搜索过滤
        if query_request.search:
            search_mask = df.astype(str).apply(
                lambda x: x.str.contains(query_request.search, case=False, na=False)
            ).any(axis=1)
            df = df[search_mask]

        # 应用自定义过滤
        if query_request.filters:
            for field, value in query_request.filters.items():
                if field in df.columns:
                    df = df[df[field].astype(str).str.contains(str(value), case=False, na=False)]

        total = len(df)

        # 分页
        start_idx = (query_request.page - 1) * query_request.size
        end_idx = start_idx + query_request.size
        df_page = df.iloc[start_idx:end_idx]

        # 转换数据格式
        contacts = []
        for _, row in df_page.iterrows():
            contact = {}
            for col in df.columns:
                contact[col] = str(row[col]) if pd.notna(row[col]) else ""
            contacts.append(contact)

        # 计算总页数
        pages = (total + query_request.size - 1) // query_request.size

        return ContactQueryResponse(
            contacts=contacts,
            total=total,
            page=query_request.page,
            size=query_request.size,
            pages=pages,
            columns=df.columns.tolist(),
            data_source_name=data_source_name,
            data_source_type=data_source_type
        )

    @staticmethod
    def get_contact_statistics(db: Session, user_id: int) -> ContactStatistics:
        """获取联系人统计信息"""
        # 总数统计
        total = db.query(Contact).filter(Contact.user_id == user_id).count()

        # 按公司统计
        company_stats = db.query(
            Contact.company,
            func.count(Contact.id).label('count')
        ).filter(
            Contact.user_id == user_id,
            Contact.company.isnot(None),
            Contact.company != ''
        ).group_by(Contact.company).limit(10).all()

        by_company = {item.company: item.count for item in company_stats}

        # 按部门统计
        department_stats = db.query(
            Contact.department,
            func.count(Contact.id).label('count')
        ).filter(
            Contact.user_id == user_id,
            Contact.department.isnot(None),
            Contact.department != ''
        ).group_by(Contact.department).limit(10).all()

        by_department = {item.department: item.count for item in department_stats}

        # 最近7天新增
        from datetime import datetime, timedelta
        seven_days_ago = datetime.now() - timedelta(days=7)
        recent_added = db.query(Contact).filter(
            Contact.user_id == user_id,
            Contact.created_at >= seven_days_ago
        ).count()

        return ContactStatistics(
            total=total,
            by_company=by_company,
            by_department=by_department,
            recent_added=recent_added
        )

    @staticmethod
    def validate_email(email: str) -> bool:
        """验证邮箱格式"""
        pattern = r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
        return re.match(pattern, email) is not None


